package com.sinosoft.cnmuseum.servlets;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

import com.sinosoft.cnmuseum.dao.MbXtglJgDao;
import com.sinosoft.cnmuseum.entity.MbXtglJg;
import com.sinosoft.cnmuseum.entity.MbXtglYhxx;
import com.sinosoft.cnmuseum.servlets.i.IServlet;
import com.sinosoft.cnmuseum.utils.LogUtils;
import com.sinosoft.cnmuseum.utils.PageUtil;
import com.sinosoft.cnmuseum.utils.Pingyin4j;
import com.sinosoft.cnmuseum.utils.Status;
import com.sinosoft.cnmuseum.utils.StringUtils;
import com.sinosoft.commontools.annotated.TransAnnotation;
import com.sinosoft.commontools.annotated.rquestMethod;
import com.sinosoft.commontools.annotated.rquestMethod.ReqMode;
import com.sinosoft.commontools.record.Record;
import com.sinosoft.dbtools.operate.DBActorMgr;
import com.sinosoft.dbtools.operate.IDBActor;
/**
 * @项目名称：cnmuseum
 * @类名称： MbXtglJgServlet
 * @类描述：机构管理
 * @创建时间：2018年12月21日  
 * @创建人：康仕森
 */
public class MbXtglJgServlet implements IServlet{
	private static Logger logger = Logger.getLogger(TestServlet.class);
	private static SimpleDateFormat dfmt=new SimpleDateFormat("yyyyMMddHHmm");
	private static List<Record> listAll = new ArrayList<Record>();
	/**
	 * 获得机构树
	 * @param request
	 * @param response
	 * @param
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataByTree(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			String sql = "select * from "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" where "+MbXtglJg.JGZX+"=1";
			String dsf = data.getStrValue(MbXtglJg.DSF);//1:合作方2:贡献方
			if(StringUtils.isNoEmpty(dsf)){
				sql += " and "+MbXtglJg.DSF+" = "+dsf+" ";
			}
			List<Record> hezuo = dao.getBySql(sql);
			List<Record> list = new ArrayList<Record>();
			for (Record re : hezuo) {
				String name = re.getStrValue(MbXtglJg.JGMC);
				String id = re.getStrValue(MbXtglJg.ZDBH);
				String pId = re.getStrValue(MbXtglJg.PARENTID);
				Record record = new Record();
				record.add("name", name);
				record.add("id", id);
				record.add("pId", pId);
				list.add(record);
			}
			result.add("data", list);
			// 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		// 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
	    return result;
	}
	/**
	 * 查询所有二级机构
	 * @param request
	 * @param response
	 * @param
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataTwoJg(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			String sql = "select zdbh,jgmc from "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" where "
					+ ""+MbXtglJg.JGZX+"=1 and "+MbXtglJg.PARENTID+"!=0 ";
			String jgmc = data.getStrValue(MbXtglJg.JGMC);
			if(StringUtils.isNoEmpty(jgmc)){
				sql += " and "+MbXtglJg.JGMC+" like '%"+jgmc+"%'";
			}
			List<Record> jgList = dao.getBySql(sql);
			result.add("data", jgList);
			// 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		// 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
	    return result;
	}
	/**
	 * 获取机构其他数据  导出信息时使用
	 * @param request
	 * @param response
	 * @param data
	 * @return
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getData(HttpServletRequest request, HttpServletResponse response, Record data) {
		// TODO Auto-generated method stub
		Record result = new Record();
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			String sql = "select * from "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" where jgzx=1 ";
			String ordersql = " order by zdbh ";
			listAll = dao.getBySql(sql+ordersql);
			System.out.println(sql+ordersql);
			// 组装数据
			result.add(Status.ROWS, listAll);
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		return result;
	}
	/**
	 * 查询当前最序列号最大值
	 * @param request
	 * @param response
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getMaxSort(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		try { 
			MbXtglJgDao dao = new MbXtglJgDao();
		    String sql = "select max(sorted) as max from " + MbXtglJg.MB_XTGL_JG_TABLE_NAME + "  where "+MbXtglJg.JGZX+"=1";
		    List<Record> count = dao.getBySql(sql);
		    int maxCount = count.get(0).getIntValue("max")+1;
		    result.add("max", maxCount);
		    // 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败信息
			result.add(Status.MSG,"查询失败！");
		}
	    return result;
	}
	/**
	 * 添加机构
	 * @param request
	 * @param response
	 * @param data
	 * @return
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record insert(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try{
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
            
			MbXtglJgDao dao = new MbXtglJgDao();
			Record jg = new Record();
			String parentid = data.getStrValue(MbXtglJg.PARENTID);
			String jgmc = data.getStrValue(MbXtglJg.JGMC);
			String jgjj = data.getStrValue(MbXtglJg.JGJJ);
			String jgjb = data.getStrValue(MbXtglJg.JGJB);
			String sorted = data.getStrValue(MbXtglJg.SORTED);
			String dsf = data.getStrValue(MbXtglJg.DSF);
			int i = (int)(Math.random()*100+1);//随机数
			String pingYin = Pingyin4j.getPingYin(jgmc);//机构名称拼音
			String jgid = pingYin + i;//机构id为机构名称拼音和数字组合
			if(StringUtils.isNoEmpty(jgid)){
				jg.add(MbXtglJg.JGID, jgid);
			}
			if(StringUtils.isNoEmpty(parentid)){
				jg.add(MbXtglJg.PARENTID, parentid);
			}
			if(StringUtils.isNoEmpty(jgmc)){
				jg.add(MbXtglJg.JGMC, jgmc);
			}
			if(StringUtils.isNoEmpty(jgjj)){
				jg.add(MbXtglJg.JGJJ, jgjj);
			}
			if(StringUtils.isNoEmpty(jgjb)){
				jg.add(MbXtglJg.JGJB, jgjb);
			}
			if(StringUtils.isNoEmpty(sorted)){
				jg.add(MbXtglJg.SORTED, sorted);
			}
			if(StringUtils.isNoEmpty(dsf)){
				jg.add(MbXtglJg.DSF, dsf);
			}
			jg.add(MbXtglJg.JGZX, 1);
			if(dao.insert(jg)){
				int zdbh = jg.getIntValue(MbXtglJg.ZDBH);
				// 事务模式下，此标识将使此次事务提交
                LogUtils logUtils = new LogUtils();
                String desc = jgmc+LogUtils.MbXtglJg_INSERT_DESC;
                logUtils.addLog(String.valueOf(LogUtils.type_insert), LogUtils.model_jggl, contributor, desc);
				result.add(Status.SATUS,Status.OK);
			}else{
				throw new Exception("插入信息失败！");
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
			// 事务回滚，事务模式下，此标识将使此次事务回滚
			result.add(Status.SATUS, Status.FAILED);
		}
	    return result;
	}
	/**
	 * 根据zdbh查询机构详情
	 * @param request
	 * @param response
	 * @param
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataById(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			Integer zdbh = data.getIntValue(MbXtglJg.ZDBH);
			Record byId = dao.getById(zdbh);
			result.add("data", byId);
			// 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		// 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
	    return result;
	}
	/**
	 * 机构修改  可以修改的字段为结构名称jgmc、结构简介jgjj、排序字段sorted.
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	@TransAnnotation(isTrans=true)
	public Record update(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
            
			MbXtglJgDao dao = new MbXtglJgDao();
			Record jg = dao.getById(data.getIntValue("zdbh"));
			if(jg!=null){
				String jgmc = data.getStrValue(MbXtglJg.JGMC);
				String jgjj = data.getStrValue(MbXtglJg.JGJJ);
				String sorted = data.getStrValue(MbXtglJg.SORTED);
				if(StringUtils.isNoEmpty(jgmc)){
					jg.add(MbXtglJg.JGMC, jgmc);
				}
				if(StringUtils.isNoEmpty(jgjj)){
					jg.add(MbXtglJg.JGJJ, jgjj);
				}
				if(StringUtils.isNoEmpty(sorted)){
					jg.add(MbXtglJg.SORTED, sorted);
				}
				if(dao.update(jg)){
					int zdbh = jg.getIntValue(MbXtglJg.ZDBH);
					// 事务模式下，此标识将使此次事务提交
	                LogUtils logUtils = new LogUtils();
	                String desc = jgmc+LogUtils.MbXtglJg_UPDATE_DESC;
	                logUtils.addLog(String.valueOf(LogUtils.type_update), LogUtils.model_jggl, contributor, desc);
					result.add(Status.SATUS,Status.OK);
				}else{
					throw new Exception("更新信息失败！");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			result.add(Status.SATUS, Status.FAILED);
		}
	    return result;
	}
	/**
	 * 注销机构  需要传递的字段为zdbh
	 * @param request
	 * @param response
	 * @param data
	 * @return
	 */
	@Override
	@rquestMethod(MethodNote = ReqMode.All)
	public Record delete(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
            HttpSession session = request.getSession(false);
            String contributor = (String) session.getAttribute(Status.SESSION_USERID);//责任者
            
			MbXtglJgDao dao = new MbXtglJgDao();
			String countsql = "select count(zdbh) as count from "+MbXtglYhxx.MB_XTGL_YHXX_TABLE_NAME+" where 1=1 ";//查询机构下是否有未注销的用户
			Record jg = dao.getById(data.getIntValue("zdbh"));
			if(jg!=null){
				String jgmc = jg.getStrValue(MbXtglJg.JGMC);
				countsql += " and "+MbXtglYhxx.SZJG+" = "+jg.getIntValue(MbXtglJg.ZDBH);
				List<Record> countlist = dao.getBySql(countsql);
				int count = countlist.get(0).getIntValue("count");//获得总记录数
				if(count==0){
					String updatesql = "update "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" set jgzx=0 where 1=1";
					updatesql += " and "+MbXtglJg.ZDBH+" = "+jg.getIntValue(MbXtglJg.ZDBH);
					IDBActor actor = DBActorMgr.getDBActor("cnmuseum");
					actor.ExeSql(updatesql);
					
	                LogUtils logUtils = new LogUtils();
	                String desc = jgmc+LogUtils.MbXtglYhxx_DELETE_DESC;
	                logUtils.addLog(String.valueOf(LogUtils.type_delete), LogUtils.model_yhgl, contributor, desc);
					result.add(Status.SATUS,Status.OK);
				}else{
					throw new Exception("该机构下还存在用户，删除信息失败");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			// 事务回滚
			result.add(Status.SATUS, Status.FAILED);
		}
	    return result;
	}
	
	/**
	 * 查询已注销的机构
	 * @param request
	 * @param response
	 * @param data
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataByWhere(HttpServletRequest request, HttpServletResponse response, Record data) {
		// TODO Auto-generated method stub
		Record result = new Record();
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			String countsql = "select count(zdbh) as count from "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" where jgzx=0";
			String sql = "select * from "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" where jgzx=0";
			String ordersql = " order by zdbh desc ";
			String limitsql = " limit ";
			int pagenumber = data.getIntValue(Status.PAGENUMBER);// 获取当前页
			int pagesize = data.getIntValue(Status.PAGESIZE);// 获取每页多少条数据
			String jgmc = data.getStrValue(MbXtglJg.JGMC);
			String jgjj = data.getStrValue(MbXtglJg.JGJJ);
			String dsf = data.getStrValue(MbXtglJg.DSF);//1:合作方2:贡献方
			if(StringUtils.isNoEmpty(dsf)){
				sql += " and "+MbXtglJg.DSF+" = "+dsf+" ";
			}
			if(StringUtils.isNoEmpty(jgmc)){
				countsql += " and "+MbXtglJg.JGMC+" like '%"+jgmc+"%'";
				sql += " and "+MbXtglJg.JGMC+" like '%"+jgmc+"%'";
			}
			if(StringUtils.isNoEmpty(jgjj)){
				countsql += " and "+MbXtglJg.JGJJ+" like '%"+jgjj+"%'";
				sql += " and "+MbXtglJg.JGJJ+" like '%"+jgjj+"%'";
			}
			List<Record> countlist = dao.getBySql(countsql);
			int total = countlist.get(0).getIntValue("count");//获得总记录数
			PageUtil page = new PageUtil(pagenumber, pagesize, total);
			limitsql += page.getStart()+","+pagesize;
			List<Record> list = dao.getBySql(sql+ordersql+limitsql);
			System.out.println(sql+ordersql+limitsql);
			// 组装数据
			result.add(Status.ROWS, list);
			result.add(Status.TOTAL, total);
			result.add(Status.PAGENUMBER, pagenumber);
			result.add(Status.PAGESIZE, pagesize);
			result.add(Status.PAGETOTAL, page.getPagetotal());
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		return result;
	}
	
	/**
	 * 导出机构数据到excel中
	 * @param request
	 * @param response
	 * @param 
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataXls(HttpServletRequest request, HttpServletResponse response,Record data) {
		Record result = new Record();
		try {
			OutputStream out = response.getOutputStream();
			response.reset();
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			String title = "机构信息";
			String filename = URLEncoder.encode(title, "UTF-8")+"_"+dfmt.format(new Date())+".xls";
			response.setHeader("Content-disposition", "attachment;filename="+filename);
	        HSSFWorkbook wb = new HSSFWorkbook();
	        //列表标题
	        HSSFSheet sheet = wb.createSheet(title);
	        HSSFSheet hs0 = wb.getSheetAt(0);
	        
	        HSSFRow row = null;
	        HSSFCell cell = null;
	        row = hs0.createRow(0);
	        row.createCell(0).setCellValue("机构ID");
	        row.createCell(1).setCellValue("机构名称");
	        row.createCell(2).setCellValue("机构父级机构");
	        row.createCell(3).setCellValue("机构简介");
	        row.createCell(4).setCellValue("机构级别");
	        row.createCell(5).setCellValue("排序编号");
	        
	        if (listAll != null && listAll.size() > 0) {
                int num = 1;
                for (int i = 0; i < listAll.size(); i++) {
                    Record record=listAll.get(i);
                    Row row1 = hs0.createRow(num++);
                    row1.createCell(0).setCellValue(record.getStrValue(MbXtglJg.JGID));
                    row1.createCell(1).setCellValue(record.getStrValue(MbXtglJg.JGMC));
                    row1.createCell(2).setCellValue(record.getStrValue(MbXtglJg.PARENTID));
                    row1.createCell(3).setCellValue(record.getStrValue(MbXtglJg.JGJJ));
                    row1.createCell(4).setCellValue(record.getStrValue(MbXtglJg.JGJB));
                    row1.createCell(5).setCellValue(record.getStrValue(MbXtglJg.SORTED));
                }
	        }
	        listAll.clear();
	      //让列宽随着导出的列长自动适应
           for (int colNum = 0; colNum < 7; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }
            }
	        wb.write(out);
	        out.close();
	        out.close();
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"导出失败！");
		}
		return result;
	}
	/**
	 * 雷俊峰
	 * 查询所有一级机构
	 * @param request
	 * @param response
	 * @param
	 * @return
	 */
	@rquestMethod(MethodNote = ReqMode.All)
	public Record getDataOneJg(HttpServletRequest request, HttpServletResponse response, Record data) {
		Record result = new Record();
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			String sql = "select zdbh,jgmc from "+MbXtglJg.MB_XTGL_JG_TABLE_NAME+" where "
					+ ""+MbXtglJg.JGZX+"=1 and "+MbXtglJg.PARENTID+"=0 ";
			String dsf = data.getStrValue(MbXtglJg.DSF);
			if(StringUtils.isNoEmpty(dsf)){
				sql += " and "+MbXtglJg.DSF+" = "+dsf+" ";
			}
			List<Record> jgList = dao.getBySql(sql);
			result.add("data", jgList);
			// 返回接口调用成功标识
			result.add(Status.SATUS,Status.OK);
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		// 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
	    return result;
	}
	
	@rquestMethod(MethodNote = ReqMode.All)
	public Record updatenozx(HttpServletRequest request, HttpServletResponse response, Record data){
		Record result = new Record();
		result.add(Status.SATUS, Status.OK);
		try {
			MbXtglJgDao dao = new MbXtglJgDao();
			int zdbh = data.getIntValue("zdbh");
			Record obj = dao.getById(zdbh);
			if(obj!=null){
				obj.add(MbXtglJg.JGZX, 1);
				dao.update(obj);
			}
		} catch (Exception e) {
			// 返回接口调用失败标识
			result.add(Status.SATUS,Status.FAILED);
			// 返回接口调用失败信息，需要自己添加
			result.add(Status.MSG,"查询失败！");
		}
		// 接口返回Record对象，框架会自动封装返回给请求result对象中的数据
	    return result;
	}
	
}
